package com.infodms.dms.dao.claim.basicData;

import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.bean.AclUserBean;
import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TtAsWrKnowledgebasePO;
import com.infodms.dms.po.TtAsWrNewsOrgPO;
import com.infodms.dms.po.TtAsWrNewsPO;
import com.infodms.dms.util.sequenceUitl.SequenceManager;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class HomePageNewsDao extends BaseDao{
	public static Logger logger = Logger.getLogger(HomePageNewsDao.class);
	private static POFactory factory = POFactoryBuilder.getInstance();
	private static final HomePageNewsDao dao = new HomePageNewsDao ();
	public static final HomePageNewsDao getInstance() {
		return dao;
	}
	/********
	 * 获取userName和公司ID
	 * @param 
	 * @return
	 */
	public  List<Map<String, Object>> getUserName(String userId){
		List<Object> params = new LinkedList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append(" select u.name,c.company_name,c.company_id from tm_company c ,tc_user u where 1=1 and c.company_id=u.company_id  ");
		if(Utility.testString(userId)){
			sb.append(" and u.user_id= ?");
			params.add(userId);
		}
		List<Map<String, Object>> list = pageQuery(sb.toString(), params,getFunName());
		return list;
	}
	
	public  List<Map<String, Object>> dutyTypeDQ(){
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from  tm_org o where o.duty_type="+Constant.DUTY_TYPE_LARGEREGION+" and o.status="+Constant.STATUS_ENABLE);

		List<Map<String, Object>> list = pageQuery(sb.toString(), null,getFunName());
		return list;
	}
	
	public   PageResult<Map<String, Object>> homepageNewsList(AclUserBean logonUser,int pageSize, int curPage,String dealerId,String newsCode,String person,String startDate,String endDate,String newsStatus,String comman) throws Exception {
		PageResult<Map<String, Object>> result = null;
		StringBuffer sql = new StringBuffer();
		 if(dealerId==null||dealerId.equals("")){//如果是OEM端
			 sql.append(" select * from tt_as_wr_news  oo where 1=1 and oo.status !="+Constant.NEWS_STATUS_3+" order by oo.is_top desc,oo.status, oo.create_date desc "); //YH 2010.12.21
		 }
		 else{//如果是经销商端
			sql.append("select distinct * from ( select * from tt_as_wr_news  oo where 1=1");		 
			sql.append(" and oo.duty_type="+Constant.VIEW_NEWS_type_3+" and oo.status !="+Constant.NEWS_STATUS_3+"\n");  //YH 2010.12.21	
			
			sql.append(" union all\n" );
			sql.append(" select *\n" );   //经销商没关闭指定区域指定售后或销售
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append(" where 1 = 1\n" );
			sql.append(" and oo.duty_type = "+Constant.VIEW_NEWS_type_1+"\n" );
			sql.append(" and oo.status != "+Constant.NEWS_STATUS_3+"\n" );
				if(dealerId!=null){
					 List<Map<String, Object>> list =viewOrgIdDealerId(dealerId);
					 StringBuffer str=new StringBuffer("");
					 for(int i=0;i<list.size();i++){
						 str.append(list.get(0).get("ROOT_ORG_ID").toString());
						 if(i!=list.size()-1){
						 str.append(",");
						 }
				String realstr = str.toString().substring(0, str.toString().length()-1);		 
				sql.append(" and oo.org_type_id like'%"+realstr+"%'");
				sql.append(" and oo.is_private ='0'");
				
				if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
					sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_2+"'");
			    }else {
				 sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_1+"' \n");
			     }					
				sql.append("union all\n" );
				sql.append("select *\n" );
				sql.append("from tt_as_wr_news oo\n" );
				sql.append("where 1 = 1\n" );
				sql.append("and oo.duty_type = "+Constant.VIEW_NEWS_type_1+" \n" );
				sql.append("and oo.status != "+Constant.NEWS_STATUS_3+" \n" );
				sql.append("and oo.org_type_id like '%"+realstr+"%' \n" );
				sql.append("and oo.msg_type = '"+Constant.MSG_TYPE_3+"'");

		     }		
			sql.append("union all\n" ); //经销商没关闭全部区域 指定售后或销售
			sql.append("  select *\n" );
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append("  where 1 = 1\n" );
			sql.append("  and oo.duty_type = "+Constant.VIEW_NEWS_type_1+" \n" );
			sql.append("  and oo.status != "+Constant.NEWS_STATUS_3+" \n" );
			sql.append("  and oo.org_type_id is null\n" );
			if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
				sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_2+"'");
		    }else {
			 sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_1+"' \n");
		    }		
			sql.append(" union all\n" );
			sql.append(" select *\n" );   //经销商没关闭全部区域消息
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append(" where 1 = 1\n" );
			sql.append(" and oo.duty_type = "+Constant.VIEW_NEWS_type_1+"\n" );
			sql.append(" and oo.status != "+Constant.NEWS_STATUS_3+"\n" );
			sql.append(" and oo.org_type_id  is null");
			sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_3+"'");
			
			sql.append(" union all select *\n"); //经销商没关闭指定了经销商指定了经销商类型
			sql.append(" from tt_as_wr_news oo where oo.news_id in (\n" );
			sql.append("select O.NEWS_ID from tt_as_wr_news_org o where o.dealer_id like '%"+logonUser.getDealerId()+"%' ");
			if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
				sql.append(" and o.msg_type ='"+Constant.MSG_TYPE_2+"'");
		    }else {
			 sql.append(" and o.msg_type ='"+Constant.MSG_TYPE_1+"'");
		    }	
			sql.append(" ) and oo.status != "+Constant.NEWS_STATUS_3);  //YH 2010.12.21
			
			 if(newsCode!=null&&!newsCode.equals("")){
				 sql.append(" and oo.NEWS_code like '%"+newsCode+"%'");
			 }
			 if(person!=null&&!person.equals("")){
				 sql.append(" and oo.voice_person='"+person+"'");
			 }
			 if(startDate!=null&&!startDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)>=to_date('"+startDate+"','yyyy-mm-dd')");
			 }
			 if(endDate!=null&&!endDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)<=to_date('"+endDate+"','yyyy-mm-dd')");
			 }
			 if(newsStatus!=null&&!newsStatus.equals("")){
				 sql.append(" and oo.status="+newsStatus);
			 }
			 if(comman!=null&&!comman.equals("")){
				 sql.append(" and oo.status="+Constant.NEWS_STATUS_1+"");
			 }
			 
		   }
		    sql.append(") order by is_top desc,status,create_date desc ");
	}		
		 logger.info("--------------"+sql.toString());
		result = (PageResult<Map<String, Object>>) pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return result;
	}
	
	 //YH 2010.12.21
	public   PageResult<Map<String, Object>> homepageNewsList2(AclUserBean logonUser,int pageSize, int curPage,String dealerId,String newsCode,String person,String startDate,String endDate,String newsStatus,String comman,String title) throws Exception {
		PageResult<Map<String, Object>> result = null;
		StringBuffer sql = new StringBuffer();
		 if(dealerId==null||dealerId.equals("")){//如果是OEM端
			 sql.append(" select * from tt_as_wr_news  oo where 1=1 "); //YH 2010.12.21
			 
			 if(newsCode!=null&&!newsCode.equals("")){
				 sql.append(" and oo.NEWS_code like '%"+newsCode+"%'");
			 }
			 if(person!=null&&!person.equals("")){
				 sql.append(" and oo.voice_person='"+person+"'");
			 }
			 if(startDate!=null&&!startDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)>=to_date('"+startDate+"','yyyy-mm-dd')");
			 }
			 if(endDate!=null&&!endDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)<=to_date('"+endDate+"','yyyy-mm-dd')");
			 }
			 if(newsStatus!=null&&!newsStatus.equals("")){
				 sql.append(" and oo.status="+newsStatus);
			 }
			 if(title!=null&&!title.equals("")){
				 sql.append(" and oo.news_title like '%"+title+"%'");
			 }
		    sql.append(" order by is_top desc,status,create_date desc ");
		 }
		 else{//如果是经销商端
			sql.append("select distinct * from ( select * from tt_as_wr_news  oo where 1=1");		 
			sql.append(" and oo.duty_type="+Constant.VIEW_NEWS_type_3+"\n");  //YH 2010.12.21	
			
			sql.append(" union all\n" );
			sql.append(" select *\n" );   //经销商没关闭指定区域指定售后或销售
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append(" where 1 = 1\n" );
			sql.append(" and oo.duty_type = "+Constant.VIEW_NEWS_type_1+"\n" );		
				if(dealerId!=null){
					 List<Map<String, Object>> list =viewOrgIdDealerId(dealerId);
					 StringBuffer str=new StringBuffer("");
					 for(int i=0;i<list.size();i++){
						 str.append(list.get(0).get("ROOT_ORG_ID").toString());
						 if(i!=list.size()-1){
						 str.append(",");
						 }
				String realstr = str.toString().substring(0, str.toString().length()-1);		 
				sql.append(" and oo.org_type_id like'%"+realstr+"%'");
				sql.append(" and oo.is_private ='0'");
				
				if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
					sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_2+"'");
			    }else {
				 sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_1+"' \n");
			     }					
				sql.append("union all\n" );
				sql.append("select *\n" );
				sql.append("from tt_as_wr_news oo\n" );
				sql.append("where 1 = 1\n" );
				sql.append("and oo.duty_type = "+Constant.VIEW_NEWS_type_1+" \n" );
				sql.append("and oo.org_type_id like '%"+realstr+"%' \n" );
				sql.append("and oo.msg_type = '"+Constant.MSG_TYPE_3+"'");

		     }		
			sql.append("union all\n" ); //经销商没关闭全部区域 指定售后或销售
			sql.append("  select *\n" );
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append("  where 1 = 1\n" );
			sql.append("  and oo.duty_type = "+Constant.VIEW_NEWS_type_1+" \n" );
			sql.append("  and oo.org_type_id is null\n" );
			if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
				sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_2+"'");
		    }else {
			 sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_1+"' \n");
		    }		
			sql.append(" union all\n" );
			sql.append(" select *\n" );   //经销商没关闭全部区域消息
			sql.append(" from tt_as_wr_news oo\n" );
			sql.append(" where 1 = 1\n" );
			sql.append(" and oo.duty_type = "+Constant.VIEW_NEWS_type_1+"\n" );
			sql.append(" and oo.org_type_id  is null");
			sql.append(" and oo.msg_type ='"+Constant.MSG_TYPE_3+"'");			
			sql.append(" union all select *\n"); //经销商没关闭指定了经销商指定了经销商类型
			sql.append(" from tt_as_wr_news oo where oo.news_id in (\n" );
			sql.append("select O.NEWS_ID from tt_as_wr_news_org o where o.dealer_id like '%"+logonUser.getDealerId()+"%' ");
			if(null != logonUser.getDealerType() && Constant.DEALER_TYPE_DWR == logonUser.getDealerType()){
				sql.append(" and o.msg_type ='"+Constant.MSG_TYPE_2+"'");
		    }else {
			 sql.append(" and o.msg_type ='"+Constant.MSG_TYPE_1+"'");
		    }	
			 sql.append(" )) oo where 1=1 ");  //YH 2010.12.21
			
			 if(newsCode!=null&&!newsCode.equals("")){
				 sql.append(" and oo.NEWS_code like '%"+newsCode+"%'");
			 }
			 if(person!=null&&!person.equals("")){
				 sql.append(" and oo.voice_person='"+person+"'");
			 }
			 if(startDate!=null&&!startDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)>=to_date('"+startDate+"','yyyy-mm-dd')");
			 }
			 if(endDate!=null&&!endDate.equals("")){
				 sql.append(" and trunc(oo.NEWS_DATE)<=to_date('"+endDate+"','yyyy-mm-dd')");
			 }
			 if(newsStatus!=null&&!newsStatus.equals("")){
				 sql.append(" and oo.status="+newsStatus);
			 }
			 if(comman!=null&&!comman.equals("")){
				 sql.append(" and oo.status="+Constant.NEWS_STATUS_1+"");
			 }
			 if(title!=null&&!title.equals("")){
				 sql.append(" and oo.news_title like '%"+title+"%'");
			 }
			 
		   }
		    sql.append(" order by is_top desc,status,create_date desc ");
	}		
		 logger.info("--------------"+sql.toString());
		result = (PageResult<Map<String, Object>>) pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return result;
	}
	/**
	 * Iverson add By 2010-12-09
	 * @param pageSize
	 * @param curPage
	 * @param dealerId
	 * @param newsCode
	 * @param person
	 * @param startDate
	 * @param endDate
	 * @param newsStatus
	 * @param comman
	 * @return
	 * @throws Exception
	 */
	public   PageResult<Map<String, Object>> homepageNewsList1(int pageSize, int curPage ,String newsCode,String person,String startDate,String endDate,String newsStatus,String comman,String title,String modelGroupId,String modelPart) throws Exception {
		PageResult<Map<String, Object>> result = null;
		StringBuffer sql = new StringBuffer();
			 sql.append(" select oo.id,oo.code,oo.title,oo.published_date,oo.contents,oo.create_date,oo.create_by,oo.status,oo.voice_person,oo.org_id,oo.org_name,oo.model_part, \n");
			 sql.append("(select c.wrgroup_name from tt_as_wr_model_group c where c.wrgroup_id=oo.wrgroup_id) as wrgroup_id from tt_as_wr_KnowledgeBase  oo where 1=1\n");
		 if(newsCode!=null&&!newsCode.equals("")){
			 sql.append(" and oo.code like '%"+newsCode+"%'");
		 }
		 if(person!=null&&!person.equals("")){
			 sql.append(" and oo.voice_person='"+person+"'");
		 }
		 if(startDate!=null&&!startDate.equals("")){
			 sql.append(" and trunc(oo.Published_DATE)>=to_date('"+startDate+"','yyyy-mm-dd')");
		 }
		 if(endDate!=null&&!endDate.equals("")){
			 sql.append(" and trunc(oo.Published_DATE)<=to_date('"+endDate+"','yyyy-mm-dd')");
		 }
		 if(newsStatus!=null&&!newsStatus.equals("")){
			 sql.append(" and oo.status="+newsStatus);
		 }
		 if(title!=null&&!title.equals("")){
			 sql.append(" and oo.title like '%"+title+"%'");
		 }
		 if(comman!=null&&!comman.equals("")){
			 sql.append(" and oo.status="+Constant.NEWS_STATUS_1+"");
		 }
		 if(modelGroupId!=null&&!modelGroupId.equals("")){
			 sql.append(" and oo.wrgroup_id="+modelGroupId+"\n");
		 }
		 if(modelPart!=null&&!modelPart.equals("")){
			 sql.append(" and oo.model_part='"+modelPart+"'\n");
		 }
		 
		 sql.append(" order by status,create_date desc ");
		 logger.info("--------------"+sql.toString());
		result = (PageResult<Map<String, Object>>) pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return result;
	}
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
	
	/******
	 * 插入首页新闻
	 * @param po
	 */
	public static void  insertNewsPO(TtAsWrNewsPO po ){

		factory.insert(po);
		
	}
	/**
	 * Iverson add By 2010-12-09
	 * 插入应用知识库
	 * @param po
	 */
	public static void  insert(TtAsWrKnowledgebasePO po ){

		factory.insert(po);
		
	}
	/*****
	 * 插入首页新闻权限表
	 * @param po
	 */
	public static void  insertNewsOrgPO(TtAsWrNewsOrgPO po ){

		factory.insert(po);
		
	}
	/*******
	 * 修改首页新闻
	 * @param po
	 * @param po1
	 */
	public static void  updateNewsPO(TtAsWrNewsPO po,TtAsWrNewsPO po1){
		
		factory.update(po, po1);
		
	}
	/**
	 * Iverson add By 2010-12-09
	 * 修改
	 * @param po
	 * @param po1
	 */
	public static void  update(TtAsWrKnowledgebasePO po,TtAsWrKnowledgebasePO po1){
		factory.update(po, po1);
	}
	
	public  List<Map<String, Object>> newslist(String newsId){
		List<Object> params = new LinkedList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select * from tt_as_wr_news where 1=1  ");
		if(Utility.testString(newsId)){
			sql.append(" and NEWS_Id= ?");
			params.add(newsId);
		}
	    logger.info(sql.toString());
		List<Map<String, Object>> list = pageQuery(sql.toString(), params,getFunName());
		return list;
	}
	
	/**
	 * Iverson add By 2010-12-09
	 * @param newsId
	 * @return
	 */
	public  List<Map<String, Object>> newslist1(String newsId){
		List<Object> params = new LinkedList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select * from tt_as_wr_KnowledgeBase where 1=1  ");
		if(Utility.testString(newsId)){
			sql.append(" and Id= ?");
			params.add(newsId);
		}
	    logger.info(sql.toString());
		List<Map<String, Object>> list = pageQuery(sql.toString(), params,getFunName());
		return list;
	}
	
	public  List<Map<String, Object>> viewDealer(String dealerCode){
		StringBuilder sql = new StringBuilder();
		sql.append(" select dealer_id from tm_dealer  where dealer_code='"+dealerCode+"' ");
	    logger.info(sql.toString());
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	/********
	 * 关闭操作
	 * @param newsId
	 */
	public void closeNews(String newsId){
		String sql =" update tt_as_wr_news set status ="+Constant.NEWS_STATUS_3+" where news_id="+newsId;
		factory.update(sql, null);
	}
	
	/**
	 * Iverson add By 2010-12-09
	 * 关闭操作
	 * @param newsId
	 */
	public void closeNews1(String newsId){
		String sql =" update tt_as_wr_KnowledgeBase set status ="+Constant.NEWS_STATUS_3+" where id="+newsId;
		factory.update(sql, null);
	}
	
	/********
	 * 关闭操作
	 * @param newsId
	 */
	public void cancelNews(String newsId){
		String sql =" update tt_as_wr_news set status ="+Constant.NEWS_STATUS_2+" where news_id="+newsId;
		factory.update(sql, null);
	}
	
	/******
	 * 
	 * 删除新闻权限表
	 */
	public void delNewsOrg(String newsId){
		String sql = "delete tt_as_wr_news_org o where o.news_id="+newsId;
		factory.delete(sql, null);
	}
	/******
	 * 
	 * 设置新闻置顶
	 */
	public void setTop(String newsId){
		StringBuffer sql= new StringBuffer();
		sql.append("update tt_as_wr_news wn set wn.is_Top=  (select max(topnum)+1 topnum  from (\n" );
		sql.append("  select max(wn.is_top)  topnum  from tt_as_wr_news wn   group by wn.is_top )),\n" );
		sql.append("  wn.update_date = sysdate where wn.news_id="+newsId);	
		factory.delete(sql.toString(), null);
	}
	/******
	 * 
	 * 设置取消新闻置顶
	 */
	public void dropTop(String newsId){
		StringBuffer sql= new StringBuffer();
		sql.append("update tt_as_wr_news wn set wn.is_Top = 0 , \n" );
		sql.append("  wn.update_date = sysdate where wn.news_id="+newsId);	
		factory.delete(sql.toString(), null);
	}
		/******
		 * 
		 * 删除新闻权限表
		 */
		public void updateNews(String newsId){
			String sql = "update tt_as_wr_news wn set wn.org_type_id='',wn.duty_type=''  where wn.news_id="+newsId;
			factory.delete(sql, null);
		}
		
		/******
		 * 
		 * 删除新闻主体 YH 2011.3.23
		 */
		public void delNews(String newsId){
			String sql = "delete tt_as_wr_news n where n.news_id="+newsId;
			factory.delete(sql, null);
		}	
		
		/******
		 * 根据用户查询出机构ID
		 * @param userId
		 * @return
		 */
		public  List<Map<String, Object>> viewOrgIdUserId(String userId){
			StringBuilder sb = new StringBuilder();
			sb.append("select org_id from tm_org where company_id=(select company_id from tc_user where user_id="+userId+")");
			logger.info(sb.toString());
			List<Map<String, Object>> list = pageQuery(sb.toString(), null,getFunName());
			return list;
		}
		/********
		 * 根据经销商ID查询出ORG信息
		 * @param dealerId
		 * @return
		 */
		public  List<Map<String, Object>> viewOrgIdDealerId(String dealerId){
			StringBuilder sb = new StringBuilder();
			sb.append("select * from VW_ORG_DEALER_SERVICE where dealer_id in ("+dealerId+")");
			sb.append(" UNION\n");
			sb.append("SELECT * FROM VW_ORG_DEALER VOD WHERE VOD.DEALER_ID IN ("+dealerId+")");
			logger.info(sb.toString());
			List<Map<String, Object>> list = pageQuery(sb.toString(), null,getFunName());
			return list;
		}
	    /*******
	     * 根据新闻ID 查询出经销商编码
	     * @param newsId
	     * @return
	     */
		public  List<Map<String, Object>> listNewsParentOrgs(String newsId){
			StringBuilder sb = new StringBuilder();
			sb.append("select dealer_code from tm_dealer where dealer_id in( select dealer_id from tt_as_wr_news_org where  news_id="+newsId+")");
			logger.info(sb.toString());
			List<Map<String, Object>> list = pageQuery(sb.toString(), null,getFunName());
			return list;
		}
		/********
		 * 根据orgId查询出ORG_CODE
		 * @param orgId
		 * @return
		 */
		public  List<Map<String, Object>> listNewsParentOrgsCode(String orgId){
			StringBuilder sb = new StringBuilder();
			sb.append("select org_code from tm_org where org_id="+orgId+"");
			logger.info(sb.toString());
			List<Map<String, Object>> list = pageQuery(sb.toString(), null,getFunName());
			return list;
		}
}
